package com.gack.business.dao;

import java.math.BigInteger;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

/**
 * 
* @ClassName: OpinionDao 
* @Description: TODO(这里用一句话描述这个类的作用) 
* @author (ZhangXinYu)  
* @date 2018年6月6日 下午4:03:59 
* @version V1.0
 */
@Repository
public class OpinionDao {
	@Autowired
	private EntityManager entityManager;
	
	public List<Opinionnobind> getOpinions(String startTime,String endTime,String source,Integer currentPage,Integer pageSize){
		List<Opinionnobind> opinions = new ArrayList<>();
		String jpql = "select "
				//+ " o.id,u.username,IFNULL(u.nickname,o.userid) as nickname,o.opiniontext,o.create_time,o.status,o.replytext "
				+ " o.id,IFNULL(u.username,o.userid) as username,IFNULL(u.nickname,'匿名用户') as nickname,o.opiniontext,o.create_time,o.status,o.replytext "
				+ " from opinion o "
				+ " left join user u on o.userid = u.id "
				+ " where 1=1 "
				+ " and o.source = :source ";
		if(startTime != null && !startTime.equals("")){
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS Z");
			try{
				Date d = format.parse(startTime.replace("Z", " UTC"));
				format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				startTime = format.format(d);
				jpql += " and o.create_time > '"+startTime+"'";
			} catch (ParseException e) {
				e.printStackTrace();
			}//注意是空格+UTC
		}
		if(endTime != null && !endTime.equals("")){
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS Z");
			try{
				Date d = format.parse(endTime.replace("Z", " UTC"));
				format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				endTime = format.format(d);
				jpql += " and o.create_time < '"+endTime+"'";
			} catch (ParseException e) {
				e.printStackTrace();
			}//注意是空格+UTC
		}
		jpql += " order by o.create_time desc";
		Query query = entityManager.createNativeQuery(jpql,Opinionnobind.class);
		query.setParameter("source", source);
		opinions = (List<Opinionnobind>)query.setFirstResult(currentPage*pageSize).setMaxResults(pageSize).getResultList();
		return opinions;
	}
	
	public long getTotalOpinionCount(String startTime,String endTime,String source,Integer currentPage,Integer pageSize){
		BigInteger count = new BigInteger("0");
		String jpql = "select count(o.id)"
				+ " from opinion o "
				+ " left join user u on o.userid = u.id "
				+ " where 1=1 "
				+ " and o.source = :source ";
		if(startTime != null && !startTime.equals("")){
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS Z");
			try{
				Date d = format.parse(startTime.replace("Z", " UTC"));
				format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				startTime = format.format(d);
				jpql += " and o.create_time > '"+startTime+"'";
			} catch (ParseException e) {
				e.printStackTrace();
			}//注意是空格+UTC
		}
		if(endTime != null && !endTime.equals("")){
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS Z");
			try{
				Date d = format.parse(endTime.replace("Z", " UTC"));
				format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				endTime = format.format(d);
				jpql += " and o.create_time < '"+endTime+"'";
			} catch (ParseException e) {
				e.printStackTrace();
			}//注意是空格+UTC
		}
		jpql += " order by o.create_time desc";
		Query query = entityManager.createNativeQuery(jpql);
		query.setParameter("source", source);
		count = (BigInteger)query.getSingleResult();
		return count.intValue();
	}
	
	public Opinionnobind getOpinionInfo(String id){
		Opinionnobind opinionnobind = null;
		String jpql = "select "
				+ " o.id,IFNULL(u.username,o.userid) as username,IFNULL(u.nickname,'匿名用户') as nickname,o.opiniontext,o.create_time,o.status,o.replytext "
				+ " from opinion o "
				+ " left join user u on o.userid = u.id "
				+ " where 1=1 "
				+ " and o.id = :id";
		Query query = entityManager.createNativeQuery(jpql,Opinionnobind.class);
		query.setParameter("id", id);
		opinionnobind = (Opinionnobind)query.getSingleResult();
		return opinionnobind;
	}
}
